﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;



namespace _1241118_DoAn_hqtcsdl
{
    public partial class TruongGa : Form
    {
        string connect = @"Data Source=QUAN\QUAN1504;Initial Catalog=QuanLyGaSG;Integrated Security=True";
        public TruongGa()
        {
            InitializeComponent();
        }

        private void Form2_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }

        //thêm chuyến tàu

        private void thêmChuyếnTàuToolStripMenuItem_Click(object sender, EventArgs e)
        {
            label1.Visible = true;
            label3.Visible = true;
            label2.Visible = false;
            label4.Visible = true;
            label6.Visible = true;
            label7.Visible = true;
            comboBox1.Visible = true;
            comboBox2.Visible = true;
            comboBox3.Visible = true;
            comboBox4.Visible = false;
            dateTimePicker1.Visible = true;
            button4.Visible = false;
            button1.Visible = true;
            button2.Visible = true;
            textBox6.Visible = true;
            groupBox2.Visible = true;
            label5.Visible = false;
            label8.Visible = false;
            textBox1.Visible = false;
            textBox2.Visible = false;
            textBox3.Visible = false;
            button3.Visible = false;
            button5.Visible = false;
            button6.Visible = false;
            button7.Visible = true;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            dataGridView2.Visible = false;
            button13.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = true;
            dataGridView3.Visible = false;
        }

        private void TruongGa_Load(object sender, EventArgs e)
        {
            SqlConnection sql = new SqlConnection(connect);
            string motaloaichuyen = "select * from MoTaLoaiChuyen";
            sql.Open();
            SqlCommand sqlcm = new SqlCommand(motaloaichuyen, sql);
            SqlDataAdapter sqldt = new SqlDataAdapter(sqlcm);
            DataTable data1 = new DataTable();
            sqldt.Fill(data1);
            comboBox1.DataSource = data1;
            comboBox1.DisplayMember = "MotaLC";
            comboBox1.ValueMember = "MaLC";

            string matau = "select * from MoTaTau";
            SqlCommand sqlcm1 = new SqlCommand(matau, sql);
            SqlDataAdapter sqldt1 = new SqlDataAdapter(sqlcm1);
            DataTable data2 = new DataTable();
            sqldt1.Fill(data2);
            comboBox2.DataSource = data2;
            comboBox2.DisplayMember = "MaTau";
            comboBox2.ValueMember = "MaTau";

            //string diadiemden = "select * from danhsachga";
            //SqlCommand sqlcm2 = new SqlCommand(diadiemden, sql);
            Connectdatabase a = new Connectdatabase();
            SqlCommand sqlcm2 = a.connectdatabase("select * from danhsachga");
            SqlDataAdapter sqldt2 = new SqlDataAdapter(sqlcm2);
            DataTable data3 = new DataTable();
            sqldt2.Fill(data3);
            comboBox3.DataSource = data3;
            comboBox3.DisplayMember = "MotaGa";
            comboBox3.ValueMember = "MotaGa";

            comboBox7.DataSource = data3;
            comboBox7.DisplayMember = "MotaGa";
            comboBox7.ValueMember = "MotaGa";



            Connectdatabase a1 = new Connectdatabase();
            SqlCommand sqlcm21 = a1.connectdatabase("select * from danhsachga");
            SqlDataAdapter sqldt21 = new SqlDataAdapter(sqlcm21);
            DataTable data31 = new DataTable();
            sqldt21.Fill(data31);
            comboBox5.DataSource = data31;
            comboBox5.DisplayMember = "MotaGa";
            comboBox5.ValueMember = "MotaGa";


            timer1.Start();
        }

        //button insert chuyến tàu
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection sqlq = new SqlConnection(connect);
            sqlq.Open();
            string sqlcomman = "INSERTINFOCHUYENTAU_FULL";
            SqlCommand sqlcm2 = new SqlCommand(sqlcomman, sqlq);

            sqlcm2.CommandType = CommandType.StoredProcedure;
            String matau = comboBox2.SelectedValue.ToString();

            //sqlcm2.Parameters.Add(new SqlParameter("@MATAU", comboBox2.ValueMember));
            sqlcm2.Parameters.Add("@MATAU", SqlDbType.NVarChar, 50).Value = matau;
            //sqlcm2.Parameters.Add(new SqlParameter("@MALC", comboBox1.ValueMember));
            sqlcm2.Parameters.Add("@MALC", SqlDbType.NVarChar, 50).Value = comboBox1.SelectedValue.ToString();
            sqlcm2.Parameters.Add("@GIODI", SqlDbType.DateTime).Value = dateTimePicker1.Value;
            sqlcm2.Parameters.Add("@DDDI", SqlDbType.NVarChar, 50).Value = textBox6.Text;
            sqlcm2.Parameters.Add("@DDDen", SqlDbType.NVarChar, 50).Value = comboBox3.SelectedValue.ToString();
            sqlcm2.ExecuteNonQuery();
            sqlcm2.Dispose();

            SqlConnection sql = new SqlConnection(connect);
            string sqlcomman1 = "select * from ThongTinChuyenTau ";
            SqlCommand sqlcm = new SqlCommand(sqlcomman1, sql);
            SqlDataAdapter sqldt = new SqlDataAdapter(sqlcm);
            DataTable data1 = new DataTable();
            sqldt.Fill(data1);
            dataGridView1.DataSource = data1;
            MessageBox.Show("Thêm Tàu Thành Công.");

        }

        //Cập Nhật chuyến tàu

        private void cậpNhậtChuyếnTàuToolStripMenuItem_Click(object sender, EventArgs e)
        {

            label1.Visible = true;
            label2.Visible = true;
            label3.Visible = true;
            label4.Visible = true;
            label6.Visible = true;
            label7.Visible = true;
            comboBox1.Visible = true;
            comboBox2.Visible = true;
            comboBox3.Visible = true;
            comboBox4.Visible = true;
            button1.Visible = false;
            dateTimePicker1.Visible = true;
            button4.Visible = true;
            button2.Visible = true;
            textBox6.Visible = true;
            groupBox2.Visible = true;
            label5.Visible = false;
            label8.Visible = false;
            textBox1.Visible = false;
            textBox2.Visible = false;
            textBox3.Visible = false;
            button3.Visible = false;
            button5.Visible = false;
            button6.Visible = false;
            button7.Visible = true;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            dataGridView2.Visible = false;
            button13.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = false;
            dataGridView3.Visible = false;
        }


        //button thông tin chuyến tàu
        private void button2_Click(object sender, EventArgs e)
        {

            Connectdatabase a = new Connectdatabase();
            SqlCommand sqlcm2 = a.connectdatabase("select * from ThongTinChuyenTau");
            SqlDataAdapter sqldt2 = new SqlDataAdapter(sqlcm2);
            DataTable data3 = new DataTable();
            sqldt2.Fill(data3);
            dataGridView1.DataSource = data3;
        }


        //button cập nhật chuyến tàu
        private void button4_Click(object sender, EventArgs e)
        {
            string maloaichuyen = Convert.ToString(comboBox4.Text);
            string matau = Convert.ToString(comboBox2.Text);
            string loaichuyen = Convert.ToString(comboBox1.Text);
            string giodi = Convert.ToString(dateTimePicker1.Value);
            string dddi = Convert.ToString(textBox6.Text);
            string ddden = Convert.ToString(comboBox3.Text);
            Connectdatabase a = new Connectdatabase();
            SqlCommand sqlcm2 = a.connectdatabase("update thongtinchuyentau set GioDi = '" + giodi + "' , DiaDiemDen = '" + ddden + "' where MaTau = '" + matau + "' and MaChuyen = '" + maloaichuyen + "'");
            sqlcm2.ExecuteNonQuery();
            MessageBox.Show("Cập Nhật Tàu Thành Công.");
        }

        //Thêm tàu
        private void thêmTàuToolStripMenuItem_Click(object sender, EventArgs e)
        {
            label1.Visible = true;
            label2.Visible = false;
            label3.Visible = false;
            label4.Visible = false;
            label6.Visible = false;
            label7.Visible = false;
            comboBox1.Visible = false;
            comboBox2.Visible = false;
            comboBox3.Visible = false;
            comboBox4.Visible = false;
            button1.Visible = false;
            textBox6.Visible = false;
            dateTimePicker1.Visible = false;
            button4.Visible = false;
            button2.Visible = false;
            label5.Visible = true;
            label8.Visible = true;
            textBox1.Visible = true;
            textBox2.Visible = true;
            textBox3.Visible = true;
            button3.Visible = true;
            button5.Visible = true;
            groupBox2.Visible = true;
            button6.Visible = false;
            button7.Visible = false;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            dataGridView2.Visible = false;
            button13.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = false;
            dataGridView3.Visible = false;
        }

        //button thêm tàu
        private void button3_Click(object sender, EventArgs e)
        {
            try
            {

                if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "")
                {
                    string text1 = textBox1.Text.ToString();
                    int text2 = Convert.ToInt32(textBox2.Text);
                    int text3 = Convert.ToInt32(textBox3.Text);
                    Connectdatabase a = new Connectdatabase();
                    SqlCommand sqlcm2 = a.connectdatabase("insert into MoTaTau values ('" + text1 + "','" + text2 + "' , '" + text3 + "' ) ");
                    sqlcm2.ExecuteNonQuery();
                }
                else
                {
                    MessageBox.Show("Điền đầy dủ thông tin để thêm  tàu.");
                }
            }
            catch
            {
                MessageBox.Show("Tàu đã tồn tại trong ga.");

            }
        }


        //button thông tin mô tả tàu
        private void button5_Click(object sender, EventArgs e)
        {

            Connectdatabase a = new Connectdatabase();
            SqlCommand sqlcm2 = a.connectdatabase("select * from motatau");
            SqlDataAdapter sqldt2 = new SqlDataAdapter(sqlcm2);
            DataTable data3 = new DataTable();
            sqldt2.Fill(data3);
            dataGridView1.DataSource = data3;

        }

        //cập nhật tàu
        private void cậpNhậtTàuToolStripMenuItem_Click(object sender, EventArgs e)
        {
            button6.Visible = true;
            label1.Visible = true;
            label5.Visible = true;
            label8.Visible = true;
            textBox1.Visible = true;
            textBox2.Visible = true;
            textBox3.Visible = true;
            label2.Visible = false;
            label3.Visible = false;
            label4.Visible = false;
            label6.Visible = false;
            label7.Visible = false;
            comboBox1.Visible = false;
            comboBox2.Visible = false;
            comboBox3.Visible = false;
            comboBox4.Visible = false;
            button1.Visible = false;
            textBox6.Visible = false;
            dateTimePicker1.Visible = false;
            button4.Visible = false;
            button2.Visible = false;
            button3.Visible = false;
            button5.Visible = true;
            groupBox2.Visible = true;
            button7.Visible = false;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            dataGridView2.Visible = false;
            button13.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = false;
            dataGridView3.Visible = false;
        }

        //button cập nhật tàu
        private void button6_Click(object sender, EventArgs e)
        {
            if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "")
            {
                string text1 = textBox1.Text.ToString();
                int text2 = Convert.ToInt32(textBox2.Text);
                int text3 = Convert.ToInt32(textBox3.Text);
                Connectdatabase a = new Connectdatabase();
                SqlCommand sqlcm2 = a.connectdatabase("update motatau set SoToa = '" + text2 + "' , SoLuongGhe = '" + text3 + "' where MaTau = '" + text1 + "'");
                sqlcm2.ExecuteNonQuery();
            }
            else
            {
                MessageBox.Show("Điền đầy dủ thông tin để chỉnh sửa thông tin tàu.");
            }
        }


        //chuyến tàu
        private void chuyếnTàuToolStripMenuItem_Click(object sender, EventArgs e)
        {
            button6.Visible = false;
            label1.Visible = true;
            label5.Visible = false;
            label8.Visible = false;
            textBox1.Visible = false;
            textBox2.Visible = false;
            textBox3.Visible = false;
            label2.Visible = true;
            label3.Visible = false;
            label4.Visible = false;
            label6.Visible = false;
            label7.Visible = false;
            comboBox1.Visible = false;
            comboBox2.Visible = true;
            comboBox3.Visible = false;
            comboBox4.Visible = true;
            button1.Visible = false;
            textBox6.Visible = false;
            dateTimePicker1.Visible = false;
            button4.Visible = false;
            button2.Visible = false;
            button3.Visible = false;
            button5.Visible = false;
            button7.Visible = true;
            groupBox2.Visible = true;
            button8.Visible = true;
            label9.Visible = true;
            label10.Visible = true;
            dateTimePicker2.Visible = true;
            button9.Visible = true;
            label11.Visible = true;
            button10.Visible = true;
            dateTimePicker3.Visible = true;
            comboBox5.Visible = true;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            dataGridView2.Visible = false;
            button13.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = false;
            dataGridView3.Visible = false;
        }

        //button tìm chuyến tàu
        private void button7_Click(object sender, EventArgs e)
        {
            Connectdatabase a1 = new Connectdatabase();
            SqlCommand sqlcm21 = a1.connectdatabase("select * from thongtinchuyentau where MaTau = '" + comboBox2.Text + "'");
            SqlDataAdapter sqldt21 = new SqlDataAdapter(sqlcm21);
            DataTable data31 = new DataTable();
            sqldt21.Fill(data31);
            comboBox4.DataSource = data31;
            comboBox4.DisplayMember = "MaChuyen";
            comboBox4.ValueMember = "MaChuyen";
            button11.Enabled = true;

        }


        //button tìm info tàu theo mã chuyến
        private void button8_Click(object sender, EventArgs e)
        {
            // Cách 1: Dùng store
            SqlConnection cn = new SqlConnection(@"Data Source=QUAN\QUAN1504;Initial Catalog=QuanLyGaSG;Integrated Security=True");
            SqlCommand sqlcm = cn.CreateCommand();
            String matau = comboBox2.SelectedValue.ToString();
            sqlcm.Parameters.Add("@MATAU", SqlDbType.NVarChar, 50).Value = matau;
            sqlcm.Parameters.Add("@MACHUYEN", SqlDbType.NVarChar, 50).Value = comboBox4.SelectedValue.ToString();

            sqlcm.CommandType = CommandType.StoredProcedure;
            sqlcm.CommandText = "INFOCHUYENTAU1";
            SqlDataAdapter sqladt = new SqlDataAdapter();
            sqladt.SelectCommand = sqlcm;
            DataTable data = new DataTable();
            sqladt.Fill(data);
            dataGridView1.DataSource = data;




            /*
                    Cách 2:
                        if (comboBox4.Text != "")
                        {

                            string sql = "INFOCHUYENTAU";
                            StringBuilder sb = new StringBuilder();
                            // Build the parameterized sql string
                            sb.Append("SELECT * ");
                            sb.Append("FROM INFOCHUYENTAU(");
                            sb.Append("@MATAU");
                            sb.Append(",@MACHUYEN");
                            sb.Append(")");

                            SqlConnection conn = new SqlConnection(connect);

                            try
                            {
                                SqlDataAdapter da = new SqlDataAdapter();
                                SqlCommand sqlCommand = new SqlCommand(sb.ToString(), conn);
                                String matau = comboBox2.SelectedValue.ToString();
                                sqlCommand.Parameters.Add("@MATAU", SqlDbType.NVarChar, 50).Value = matau;
                                sqlCommand.Parameters.Add("@MACHUYEN", SqlDbType.NVarChar, 50).Value = comboBox4.SelectedValue.ToString();

                                da.SelectCommand = sqlCommand;
                                da.SelectCommand.CommandType = CommandType.Text;
                                DataSet ds = new DataSet();

                                //Fill dữ liệu truy vấn dc vào dataset, đặt tên cho table truy vấn là "result_name"
                                da.Fill(ds, "result_name");
                                //Lấy ra DataTable
                                DataTable dt = ds.Tables["result_name"];
                                dataGridView1.DataSource = dt;
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("Error: " + ex);
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                        else
                        {
                            MessageBox.Show("Chọn Mã Tàu.");

                        }
        
        
             */
        }


        private void tìnhTrạngVéToolStripMenuItem_Click(object sender, EventArgs e)
        {
            button6.Visible = false;
            label1.Visible = true;
            label5.Visible = false;
            label8.Visible = false;
            textBox1.Visible = false;
            textBox2.Visible = false;
            textBox3.Visible = false;
            label2.Visible = true;
            label3.Visible = false;
            label4.Visible = false;
            label6.Visible = false;
            label7.Visible = false;
            comboBox1.Visible = false;
            comboBox2.Visible = true;
            comboBox3.Visible = false;
            comboBox4.Visible = true;
            button1.Visible = false;
            textBox6.Visible = false;
            dateTimePicker1.Visible = false;
            button4.Visible = false;
            button2.Visible = false;
            button3.Visible = false;
            button5.Visible = false;
            button7.Visible = true;
            groupBox2.Visible = true;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = true;
            label12.Visible = true;
            label13.Visible = true;
            label14.Visible = true;
            textBox4.Visible = true;
            textBox5.Visible = true;
            label15.Visible = true;
            label16.Visible = true;
            label17.Visible = true;
            label18.Visible = true;
            textBox7.Visible = true;
            textBox8.Visible = true;
            textBox9.Visible = true;
            button12.Visible = true;
            dataGridView2.Visible = true;
            button13.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = false;
            dataGridView3.Visible = false;
        }
        //show info chuyến tàu trong ngày
        private void button9_Click(object sender, EventArgs e)
        {
            SqlConnection a1 = new SqlConnection(connect);
            a1.Open();

            string sqlcomman = "SHOWTHONGTINCHUYENTAUDI_INNGAY";
            SqlCommand sqlcm21 = new SqlCommand(sqlcomman, a1);
            sqlcm21.CommandType = CommandType.StoredProcedure;
            sqlcm21.Parameters.Add("@DATETIME", SqlDbType.DateTime).Value = dateTimePicker2.Value;
            sqlcm21.ExecuteNonQuery();
            //           sqlcm21.Dispose();
            SqlDataAdapter x = new SqlDataAdapter(sqlcm21);
            DataTable datax = new DataTable();
            x.Fill(datax);
            dataGridView1.DataSource = datax;



            //Connectdatabase a = new Connectdatabase();
            //SqlCommand sqlcm2 = a.connectdatabase("SELECT * FROM THONGTINCHUYENTAU WHERE CONVERT(DATE,GIODI) = CONVERT(DATE,'" + dateTimePicker2.Value + "')");
            //SqlDataAdapter sqldt2 = new SqlDataAdapter(sqlcm2);
            //DataTable data3 = new DataTable();
            //sqldt2.Fill(data3);
            //dataGridView1.DataSource = data3;
        }

        //Tìm thông tin chuyến tàu đến trạm trong ngày
        private void button10_Click(object sender, EventArgs e)
        {
            Connectdatabase a = new Connectdatabase();
            SqlCommand sqlcm2 = a.connectdatabase("SELECT * FROM DSThoiGianTGTQ WHERE  DDDenTram = '" + comboBox5.Text + "'   and CONVERT(DATE,TGDenTram) = CONVERT(DATE,'" + dateTimePicker3.Value + "')");
            SqlDataAdapter sqldt2 = new SqlDataAdapter(sqlcm2);
            DataTable data3 = new DataTable();
            sqldt2.Fill(data3);
            dataGridView1.DataSource = data3;
        }

        //show thông tin ghế 
        private void button11_Click(object sender, EventArgs e)
        {
            if (comboBox4.Text != "")
            {
                Connectdatabase a = new Connectdatabase();
                SqlCommand sqlcm2 = a.connectdatabase("Select * from ThongTinGhe where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "' ");
                SqlDataAdapter sqldt2 = new SqlDataAdapter(sqlcm2);
                DataTable data3 = new DataTable();
                sqldt2.Fill(data3);
                dataGridView2.DataSource = data3;


                Connectdatabase a1 = new Connectdatabase();
                SqlCommand sqlcm21 = a1.connectdatabase("Select count(*) from ThongTinGhe where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "' and TinhTrang = 'Yes' ");
                string kq = null;
                kq = sqlcm21.ExecuteScalar().ToString();
                sqlcm21.Dispose();
                textBox4.Text = kq;

                Connectdatabase a11 = new Connectdatabase();
                SqlCommand sqlcm211 = a1.connectdatabase("Select count(*) from ThongTinGhe where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "' and TinhTrang = 'NO' ");
                string kq1 = null;
                kq1 = sqlcm211.ExecuteScalar().ToString();
                sqlcm211.Dispose();
                textBox5.Text = kq1;


            }
            else
            {
                MessageBox.Show("Chọn Mã Chuyến.");

            }
        }

        //delete ghế
        private void button12_Click(object sender, EventArgs e)
        {
            if (textBox7.Text != "" && textBox8.Text != "" && textBox9.Text != "")
            {
                string text1 = textBox7.Text.ToString();
                //string text2 = textBox8.Text.ToString();
                //string text3 = textBox9.Text.ToString();
                int text2 = Convert.ToInt32(textBox8.Text);
                int text3 = Convert.ToInt32(textBox9.Text);


                Connectdatabase a = new Connectdatabase();
                SqlCommand sqlcm2 = a.connectdatabase("delete from ThongTinGhe where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "' and MaToa = '" + text1 + "' and STTToa = '" + text2 + "' and STTGhe = '" + text3 + "' ");
                sqlcm2.ExecuteNonQuery();

                Connectdatabase a1 = new Connectdatabase();
                SqlCommand sqlcm21 = a1.connectdatabase("Select * from ThongTinGhe where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "' ");
                SqlDataAdapter sqldt21 = new SqlDataAdapter(sqlcm21);
                DataTable data31 = new DataTable();
                sqldt21.Fill(data31);
                dataGridView2.DataSource = data31;

            }
            else
            {
                MessageBox.Show("Điền đầy dủ thông tin để chỉnh sửa.");
            }
        }

        private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            textBox7.Text = dataGridView2.Rows[e.RowIndex].Cells[2].Value.ToString();
            textBox8.Text = dataGridView2.Rows[e.RowIndex].Cells[3].Value.ToString();
            textBox9.Text = dataGridView2.Rows[e.RowIndex].Cells[4].Value.ToString();
        }

        //xóa chuyến tàu
        private void xóaChuyếnTàuToolStripMenuItem_Click(object sender, EventArgs e)
        {
            button6.Visible = false;
            label1.Visible = true;
            label5.Visible = false;
            label8.Visible = false;
            textBox1.Visible = false;
            textBox2.Visible = false;
            textBox3.Visible = false;
            label2.Visible = true;
            label3.Visible = false;
            label4.Visible = false;
            label6.Visible = false;
            label7.Visible = false;
            comboBox1.Visible = false;
            comboBox2.Visible = true;
            comboBox3.Visible = false;
            comboBox4.Visible = true;
            button1.Visible = false;
            textBox6.Visible = false;
            dateTimePicker1.Visible = false;
            button4.Visible = false;
            button2.Visible = false;
            button3.Visible = false;
            button5.Visible = false;
            button7.Visible = true;
            groupBox2.Visible = true;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            button13.Visible = true;
            dataGridView2.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = true;
            label21.Visible = true;
            comboBox7.Visible = true;
            textBox10.Visible = true;
            button16.Visible = true;
            button17.Visible = true;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = false;
            dataGridView3.Visible = false;
        }
        //button delete chuyến tàu
        private void button13_Click(object sender, EventArgs e)
        {
            try
            {
                if (comboBox4.Text != "")
                {
                    SqlConnection sqlq = new SqlConnection(connect);
                    sqlq.Open();
                    string sqlcomman = "DELETE_THONGTINCHUYENTAU";
                    SqlCommand sqlcm2 = new SqlCommand(sqlcomman, sqlq);

                    sqlcm2.CommandType = CommandType.StoredProcedure;

                    sqlcm2.Parameters.Add("@MACHUYEN", SqlDbType.NVarChar, 50).Value = comboBox4.SelectedValue.ToString();
                    sqlcm2.ExecuteNonQuery();
                    sqlcm2.Dispose();

                    SqlConnection sql = new SqlConnection(connect);
                    string sqlcomman1 = "select * from ThongTinChuyenTau ";
                    SqlCommand sqlcm = new SqlCommand(sqlcomman1, sql);
                    SqlDataAdapter sqldt = new SqlDataAdapter(sqlcm);
                    DataTable data1 = new DataTable();
                    sqldt.Fill(data1);
                    dataGridView1.DataSource = data1;
                }
                else
                {
                    MessageBox.Show("Nhập Mã Chuyến.");
                }
            }
            catch
            {
                MessageBox.Show("Không Thể Xóa Chuyến Tàu Này.");
            }
        }
        //toa
        private void toaToolStripMenuItem_Click(object sender, EventArgs e)
        {
            button6.Visible = false;
            label1.Visible = true;
            label5.Visible = false;
            label8.Visible = false;
            textBox1.Visible = false;
            textBox2.Visible = false;
            textBox3.Visible = false;
            label2.Visible = true;
            label3.Visible = false;
            label4.Visible = false;
            label6.Visible = false;
            label7.Visible = false;
            comboBox1.Visible = false;
            comboBox2.Visible = true;
            comboBox3.Visible = false;
            comboBox4.Visible = true;
            button1.Visible = false;
            textBox6.Visible = false;
            dateTimePicker1.Visible = false;
            button4.Visible = false;
            button2.Visible = false;
            button3.Visible = false;
            button5.Visible = false;
            button7.Visible = true;
            groupBox2.Visible = true;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            button13.Visible = false;
            dataGridView2.Visible = false;
            button14.Visible = true;
            label19.Visible = true;
            comboBox6.Visible = true;
            button15.Visible = true;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = false;
            dataGridView3.Visible = false;
        }
        //button show thong tin toa
        private void button15_Click(object sender, EventArgs e)
        {
            Connectdatabase a11 = new Connectdatabase();
            SqlCommand sqlcm211 = a11.connectdatabase("select * from thongtinToa where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "'");
            SqlDataAdapter sqldt211 = new SqlDataAdapter(sqlcm211);
            DataTable data311 = new DataTable();
            sqldt211.Fill(data311);
            comboBox6.DataSource = data311;
            comboBox6.DisplayMember = "STTToa";
            comboBox6.ValueMember = "STTToa";
        }
        //button xóa toa
        private void button14_Click(object sender, EventArgs e)
        {

            Connectdatabase a2 = new Connectdatabase();
            SqlCommand sqlcm22 = a2.connectdatabase("delete from ThongTinGhe where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "' and STTToa = '" + comboBox6.Text + "'");
            sqlcm22.ExecuteNonQuery();

            Connectdatabase a = new Connectdatabase();
            SqlCommand sqlcm2 = a.connectdatabase("delete from ThongTinToa where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "' and STTToa = '" + comboBox6.Text + "'");
            sqlcm2.ExecuteNonQuery();

            Connectdatabase a1 = new Connectdatabase();
            SqlCommand sqlcm21 = a1.connectdatabase("Select * from ThongTinToa where MaTau = '" + comboBox2.Text + "' and MaChuyen = '" + comboBox4.Text + "' ");
            SqlDataAdapter sqldt21 = new SqlDataAdapter(sqlcm21);
            DataTable data31 = new DataTable();
            sqldt21.Fill(data31);
            dataGridView1.DataSource = data31;
        }
        //giá vé
        private void giáVéToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            button6.Visible = false;
            label1.Visible = false;
            label5.Visible = false;
            label8.Visible = false;
            textBox1.Visible = false;
            textBox2.Visible = false;
            textBox3.Visible = false;
            label2.Visible = false;
            label3.Visible = false;
            label4.Visible = false;
            label6.Visible = false;
            label7.Visible = false;
            comboBox1.Visible = false;
            comboBox2.Visible = false;
            comboBox3.Visible = false;
            comboBox4.Visible = false;
            button1.Visible = false;
            textBox6.Visible = false;
            dateTimePicker1.Visible = false;
            button4.Visible = false;
            button2.Visible = false;
            button3.Visible = false;
            button5.Visible = false;
            button7.Visible = false;
            groupBox2.Visible = true;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            button13.Visible = false;
            dataGridView2.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = true;
            label21.Visible = true;
            comboBox7.Visible = true;
            textBox10.Visible = true;
            button16.Visible = true;
            button17.Visible = true;
            button18.Visible = true;
            button19.Visible = false;
            dataGridView3.Visible = false;
        }

        private void comboBox7_SelectedIndexChanged(object sender, EventArgs e)
        {
            Connectdatabase a1 = new Connectdatabase();
            SqlCommand sqlcm21 = a1.connectdatabase("select GiaVe from danhsachga where MoTaGa = N'" + comboBox7.Text + "'");
            string kq = null;
            kq = sqlcm21.ExecuteScalar().ToString();
            sqlcm21.Dispose();
            textBox10.Text = kq;
        }

        private void button16_Click(object sender, EventArgs e)
        {
            SqlConnection sql = new SqlConnection(connect);
            string sqlcomman1 = "select * from DanhSachga ";
            SqlCommand sqlcm = new SqlCommand(sqlcomman1, sql);
            SqlDataAdapter sqldt = new SqlDataAdapter(sqlcm);
            DataTable data1 = new DataTable();
            sqldt.Fill(data1);
            dataGridView1.DataSource = data1;
        }

        private void button17_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection cn = new SqlConnection(@"Data Source=QUAN\QUAN1504;Initial Catalog=QuanLyGaSG;Integrated Security=True");
                SqlCommand sqlcm = cn.CreateCommand();
                int text2 = Convert.ToInt32(textBox10.Text);
                sqlcm.Parameters.Add("@MOTAGA", SqlDbType.NVarChar, 50).Value = comboBox7.Text.ToString();
                sqlcm.Parameters.Add("@GIATHAYDOI", SqlDbType.Int, 50).Value = text2;

                sqlcm.CommandType = CommandType.StoredProcedure;
                sqlcm.CommandText = "USP_CAPNHATGIAVE";
                SqlDataAdapter sqladt = new SqlDataAdapter();
                sqladt.SelectCommand = sqlcm;
                DataTable data = new DataTable();
                sqladt.Fill(data);
                dataGridView1.DataSource = data;
            }
            catch
            {
                SqlConnection sql = new SqlConnection(connect);
                string sqlcomman1 = "select * from DanhSachga ";
                SqlCommand sqlcm = new SqlCommand(sqlcomman1, sql);
                SqlDataAdapter sqldt = new SqlDataAdapter(sqlcm);
                DataTable data1 = new DataTable();
                sqldt.Fill(data1);
                dataGridView1.DataSource = data1;
            }
        }

        private void button18_Click(object sender, EventArgs e)
        {
            Connectdatabase a = new Connectdatabase();
            SqlCommand sqlcm2 = a.connectdatabase("update DanhSacHGa set Giave = '" + textBox10.Text + "' where MoTaGa = N'" + comboBox7.Text + "'");
            sqlcm2.ExecuteNonQuery();
        }

        private void button19_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection sqlq = new SqlConnection(connect);
                sqlq.Open();
                string sqlcomman = "INSERTINFOCHUYENTAU_DIRTY_FULL";
                SqlCommand sqlcm2 = new SqlCommand(sqlcomman, sqlq);

                sqlcm2.CommandType = CommandType.StoredProcedure;
                String matau = comboBox2.SelectedValue.ToString();

                //sqlcm2.Parameters.Add(new SqlParameter("@MATAU", comboBox2.ValueMember));
                sqlcm2.Parameters.Add("@MATAU", SqlDbType.NVarChar, 50).Value = matau;
                //sqlcm2.Parameters.Add(new SqlParameter("@MALC", comboBox1.ValueMember));
                sqlcm2.Parameters.Add("@MALC", SqlDbType.NVarChar, 50).Value = comboBox1.SelectedValue.ToString();
                sqlcm2.Parameters.Add("@GIODI", SqlDbType.DateTime).Value = dateTimePicker1.Value;
                sqlcm2.Parameters.Add("@DDDI", SqlDbType.NVarChar, 50).Value = textBox6.Text;
                sqlcm2.Parameters.Add("@DDDen", SqlDbType.NVarChar, 50).Value = comboBox3.SelectedValue.ToString();
                sqlcm2.ExecuteNonQuery();
                sqlcm2.Dispose();
            }
            catch
            {
                SqlConnection sql = new SqlConnection(connect);
                string sqlcomman1 = "select * from ThongTinChuyenTau ";
                SqlCommand sqlcm = new SqlCommand(sqlcomman1, sql);
                SqlDataAdapter sqldt = new SqlDataAdapter(sqlcm);
                DataTable data1 = new DataTable();
                sqldt.Fill(data1);
                dataGridView1.DataSource = data1;
            }
        }

        private void kháchHàngToolStripMenuItem_Click(object sender, EventArgs e)
        {
            button6.Visible = false;
            label1.Visible = false;
            label5.Visible = false;
            label8.Visible = false;
            textBox1.Visible = false;
            textBox2.Visible = false;
            textBox3.Visible = false;
            label2.Visible = false;
            label3.Visible = false;
            label4.Visible = false;
            label6.Visible = false;
            label7.Visible = false;
            comboBox1.Visible = false;
            comboBox2.Visible = false;
            comboBox3.Visible = false;
            comboBox4.Visible = false;
            button1.Visible = false;
            textBox6.Visible = false;
            dateTimePicker1.Visible = false;
            button4.Visible = false;
            button2.Visible = false;
            button3.Visible = false;
            button5.Visible = false;
            button7.Visible = false;
            groupBox2.Visible = false;
            button8.Visible = false;
            label9.Visible = false;
            label10.Visible = false;
            dateTimePicker2.Visible = false;
            button9.Visible = false;
            label11.Visible = false;
            button10.Visible = false;
            dateTimePicker3.Visible = false;
            comboBox5.Visible = false;
            button11.Visible = false;
            label12.Visible = false;
            label13.Visible = false;
            label14.Visible = false;
            textBox4.Visible = false;
            textBox5.Visible = false;
            label15.Visible = false;
            label16.Visible = false;
            label17.Visible = false;
            label18.Visible = false;
            textBox7.Visible = false;
            textBox8.Visible = false;
            textBox9.Visible = false;
            button12.Visible = false;
            button13.Visible = false;
            dataGridView2.Visible = false;
            button14.Visible = false;
            label19.Visible = false;
            comboBox6.Visible = false;
            button15.Visible = false;
            label20.Visible = false;
            label21.Visible = false;
            comboBox7.Visible = false;
            textBox10.Visible = false;
            button16.Visible = false;
            button17.Visible = false;
            button18.Visible = false;
            button19.Visible = false;
            dataGridView3.Visible = true;

            //Show Thông Tin Khách Hàng

            SqlConnection a1 = new SqlConnection(connect);
            a1.Open();

            string sqlcomman = "USP_SHOWINFOKHACHHANG";
            SqlCommand sqlcm21 = new SqlCommand(sqlcomman, a1);
            sqlcm21.CommandType = CommandType.StoredProcedure;
  //          sqlcm21.ExecuteNonQuery();
            sqlcm21.Dispose();
            SqlDataAdapter x = new SqlDataAdapter(sqlcm21);
            DataTable datax = new DataTable();
            x.Fill(datax);
            dataGridView3.DataSource = datax;


        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            label22.Text = DateTime.Now.ToString();
        }



    }
}
